package databaseHelper;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import Misc.Variables;
import Misc.Functions;


import datatypes.Exam;
import datatypes.WorkedExam;

/**
 * DAO to the exam table
 * @author Rupert
 *
 */
public class ExamDAO {
	
	DataSource ds = DataSource.INSTANCE;

	/**
	 * 
	 * Returns the number of exercises that are contained in one Exam
	 * 
	 * @param examId
	 * @return
	 */
	public int getNumberOfExercises(int examId){
		String query = "SELECT count(*) FROM multipleChoiceExercise " +
				"WHERE examId = "+examId;
		
		int numberOfExercises = 0;
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			if(rs.next())
				numberOfExercises = rs.getInt(1);
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		
		// TODO: Add other exerciseTypes
		query = "SELECT count(*) FROM imageMultipleChoiceExercise " +
				"WHERE examId = "+examId;
		
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			if(rs.next())
				numberOfExercises = numberOfExercises +  rs.getInt(1);
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		
		query = "SELECT count(*) FROM freeTextExercise " +
				"WHERE examId = "+examId;
		
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			if(rs.next())
				numberOfExercises = numberOfExercises +  rs.getInt(1);
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		
		return numberOfExercises;
	}
	
	/**
	 * 
	 * @param examId
	 * @return
	 */
	public double[] getMaxPointsArray(int examId){
		
		
		int nbrOfExercises = getNumberOfExercises(examId);
		
		
		double[] maxPointsArray = new double[nbrOfExercises];
		
		String query = 
				"(SELECT maxPointsInExercise, exerciseNumber" +
				" FROM imageMultipleChoiceExercise" +
				" WHERE examId ="+examId+")" +
				" UNION" +
				" (SELECT maxPointsInExercise, exerciseNumber" +
				" FROM multipleChoiceExercise" +
				" WHERE examId ="+examId+")" +
				" UNION" +
				" (SELECT maxPointsInExercise, exerciseNumber" +
				" FROM freeTextExercise" +
				" WHERE examId ="+examId+")" +
				" ORDER BY exerciseNumber ASC";
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			
			int i=0;
			while(rs.next() && i<maxPointsArray.length){
				double maxPoints = rs.getDouble(1);
				maxPointsArray[i] = maxPoints;
				i++;
			}
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		
//		try{
//			Statement st = ds.geatStatement();
//			ResultSet rs = st.executeQuery(query);
//			
//			rs.next();
//			Array sqlArray = rs.getArray(1);
//			int numberOfExercises = rs.getInt(2);
//			
//			maxPointsArray = new double[numberOfExercises];
//
//			ResultSet tmp = sqlArray.getResultSet();
//			
//			int i=0;
//			while(tmp.next() && i<maxPointsArray.length){
//				maxPointsArray[i] = tmp.getDouble(2);
//				i++;
//			}
//			
//		}catch(SQLException e){
//			e.printStackTrace();
//		}finally {
//			ds.close();
//		}
		
		return maxPointsArray;
		
	}
	
	/**
	 * calculates the list all exams that belong to the given user
	 *
	 * @param userId
	 * @return
	 */
	public List<Exam> findAllofUser(int userId) {
		List<Exam> allExams = new ArrayList<Exam>();
		
		String query = "SELECT * FROM exam,exam_user_rel " +
				"WHERE exam.Id = exam_user_rel.subjectId " +
				"AND exam_user_rel.userId = "+userId;
		
		try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			
			Exam curExam;
			
			while( rs.next()){
				curExam = new Exam();
				curExam.setId(rs.getInt(1));
				curExam.setSubjectIdentification(rs.getString(2));
				curExam.setSubjectName(rs.getString(3));
				curExam.setExamDate(rs.getTimestamp(4));
				curExam.setEvaluationBeginDate(rs.getTimestamp(5));
				curExam.setEvaluationEndDate(rs.getTimestamp(6));
				curExam.setNumberOfParticipants(rs.getInt(7));
				curExam.setNumberOfExercises(getNumberOfExercises(rs.getInt(1)));
				allExams.add(curExam);
			}
				
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			ds.close();
		}
		return allExams;
    }
	
	
	public int getNumberOfExamsInPast(int userId){
		
		java.sql.Timestamp sqlToday =
		        new java.sql.Timestamp(java.lang.System.currentTimeMillis());
		
		String query = "SELECT count(*) FROM exam,exam_user_rel " +
				"WHERE exam.Id = exam_user_rel.subjectId " +
				"AND exam_user_rel.userId = "+userId+" " +
				"AND exam.evaluationEnd < '"+sqlToday.toString()+"';";
		
		System.out.println(query);
		
		int nbrOfExamsInPast = 0;
		
		try {

			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			nbrOfExamsInPast = rs.getInt(1);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		
		return nbrOfExamsInPast;
	}
	
	
	public int getNumberOfExamsCurrent(int userId){
		
		java.sql.Timestamp sqlToday =
		        new java.sql.Timestamp(java.lang.System.currentTimeMillis());
		
		String query = "SELECT count(*) FROM exam,exam_user_rel " +
				"WHERE exam.Id = exam_user_rel.subjectId " +
				"AND exam_user_rel.userId = "+userId+" " +
				"AND exam.evaluationEnd > '"+sqlToday.toString()+"' " +
				"AND exam.evaluationBegin < '"+sqlToday.toString()+"';";
		
		System.out.println(query);
		
		int nbrOfExamsCurrently = 0;
		
		try {

			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			nbrOfExamsCurrently = rs.getInt(1);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		
		return nbrOfExamsCurrently;
	}
	
public int getNumberOfExamsFuture(int userId){
		
		java.sql.Timestamp sqlToday =
		        new java.sql.Timestamp(java.lang.System.currentTimeMillis());
		
		String query = "SELECT count(*) FROM exam,exam_user_rel " +
				"WHERE exam.Id = exam_user_rel.subjectId " +
				"AND exam_user_rel.userId = "+userId+" " +
				"AND exam.evaluationBegin > '"+sqlToday.toString()+"';";
		
		System.out.println(query);
		
		int nbrOfExamsInFuture = 0;
		
		try {

			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery(query);
			rs.next();
			nbrOfExamsInFuture = rs.getInt(1);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
			
		
		return nbrOfExamsInFuture;
	}
	
     
	/**
	 * Deleting the given exam from the database
	 * @param ex
	 * @return true if deleting was good
	 */
    public boolean delete(Exam ex) {
    	return deleteExamWithId(ex.getId());
    }
    
    /**
	 * Deleting the given exam from the database
	 * @param ex
	 * @return true if deleting was good
	 */
    public boolean deleteExamWithId(int id) {
    	boolean insertCorrect = execute("DELETE FROM exam WHERE Id ='"+ id + "'");
    	if(insertCorrect){
    		insertCorrect = execute("DELETE FROM exam_user_rel WHERE subjectId ='"+ id + "'");
    		insertCorrect = execute("DELETE FROM exam_grading WHERE examId ='"+ id + "'");

    		
    		deleteMpChoiceExercises(id);
    		
    		deleteImageMPChoiceExercises(id);
    		
    		deleteFreeTextMpChoiceExercise(id);
    		
    		WorkedExamDAO workedExamDAO = new WorkedExamDAO();
    		List<WorkedExam> workedExams = workedExamDAO.findAllofExam(id);
    		
    		insertCorrect = execute("DELETE FROM workedExam WHERE examId ='"+ id + "'");
    		
    		for(WorkedExam workedExam : workedExams){
        		insertCorrect = 
        				execute("DELETE FROM workedExam_archievedPoints " +
        						"WHERE workedExamId ='"+ workedExam.getId() + "'");
        		insertCorrect = 
                				execute("DELETE FROM workedExam_archievedPoints_second " +
                						"WHERE workedExamId ='"+ workedExam.getId() + "'");
    		}
    		

			String savingSpaceBase = Variables.SAVINGSPACE_EXAM + "examId"+id + "/";;
			File path = new File(savingSpaceBase);
			Functions.deleteDirectory(path);
    		
    	}
    	return insertCorrect;
    }
     
    

	private boolean deleteImageMPChoiceExercises(int examId) {
    	boolean insertCorrect = false;
    	
    	List<Integer> mpChoiceExerciseId = new ArrayList<Integer>();
		List<Integer> mpChoiceQuestionId = new ArrayList<Integer>();
		try {
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery("SELECT id FROM imageMultipleChoiceExercise WHERE examId ="+ examId);
			while(rs.next())
				mpChoiceExerciseId.add(rs.getInt(1));
			
			insertCorrect = execute("DELETE FROM imageMultipleChoiceExercise WHERE examId ="+ examId);

			if(mpChoiceExerciseId.isEmpty())
				return false;
			
			for(int exerciseId : mpChoiceExerciseId){
				
				st = ds.getStatement();
				rs = st.executeQuery("SELECT id FROM imageMultipleChoiceQuestion WHERE exerciseId ="+ exerciseId);
				
				while(rs.next())
					mpChoiceQuestionId.add(rs.getInt(1));
				
		    	insertCorrect = execute("DELETE FROM imageMultipleChoiceQuestion WHERE exerciseId ="+ exerciseId);
		    		
		    	if(mpChoiceQuestionId.isEmpty())
					return false;
					
		    	for(int questionId : mpChoiceQuestionId){
			    	insertCorrect = execute("DELETE FROM imageMultipleChoiceQuestionAnswerPossibilities WHERE imageMultipleChoiceQuestionId ="+ questionId);
			    	insertCorrect = execute("DELETE FROM imageMultipleChoiceQuestionSolutions WHERE imageMultipleChoiceQuestionId ="+ questionId);
		    	}
		    	
			    String savingSpaceBase = Variables.SAVINGSPACE_IMAGEEXERCISE + "exerciseId"+exerciseId + "/";;
				File path = new File(savingSpaceBase);
				Functions.deleteDirectory(path);
				
			}
    		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return insertCorrect;
		
	}

	private boolean deleteMpChoiceExercises(int examId) {
    	boolean insertCorrect;
    	
    	List<Integer> mpChoiceExerciseId = new ArrayList<Integer>();
		List<Integer> mpChoiceQuestionId = new ArrayList<Integer>();
		try {
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery("SELECT id FROM multipleChoiceExercise WHERE examId ="+ examId);
			while(rs.next())
				mpChoiceExerciseId.add(rs.getInt(1));
			
			insertCorrect = execute("DELETE FROM multipleChoiceExercise WHERE examId ="+ examId);

			if(mpChoiceExerciseId.isEmpty())
				return false;
			
			for(int exerciseId : mpChoiceExerciseId){
			st = ds.getStatement();
			rs = st.executeQuery("SELECT id FROM multipleChoiceQuestion WHERE exerciseId ="+ exerciseId);
				
				while(rs.next())
					mpChoiceQuestionId.add(rs.getInt(1));
				
		    	insertCorrect = execute("DELETE FROM multipleChoiceQuestion WHERE exerciseId ="+ exerciseId);
		    		
		    	if(mpChoiceQuestionId.isEmpty())
					return false;
					
		    	for(int questionId : mpChoiceQuestionId){
			    	insertCorrect = execute("DELETE FROM multipleChoiceQuestionAnswerPossibilities WHERE multipleChoiceQuestionId ="+ questionId);
			    	insertCorrect = execute("DELETE FROM multipleChoiceQuestionSolutions WHERE multipleChoiceQuestionId ="+ questionId);
		    	}
			}
    		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return true;
	}
	

    private boolean deleteFreeTextMpChoiceExercise(int examId) {
    	boolean insertCorrect;
    	
    	List<Integer> freeTextExerciseId = new ArrayList<Integer>();
		try {
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery("SELECT id FROM freeTextExercise WHERE examId ="+ examId);
			while(rs.next())
				freeTextExerciseId.add(rs.getInt(1));
			
			insertCorrect = execute("DELETE FROM freeTextExercise WHERE examId ="+ examId);

			for(int exerciseId : freeTextExerciseId){
				execute("DELETE FROM freeTextQuestion WHERE exerciseId ="+ exerciseId);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

    	return true;
	}

	/**
     * 
     * Insert a given Exam into the databse
     * 
     * @param ex
     * @param userId
     * @return
     */
    public boolean insert(Exam ex, int userId) {
    	boolean insertCorrect = execute("INSERT INTO exam(subjectId, subjectName, examDate, evaluationBegin, evaluationEnd) " +
				"VALUES ('"+ex.getSubjectIdentification()+"','"+ex.getSubjectName() +"','"
				+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ex.getExamDate()) +"','" +
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ex.getEvaluationBeginDate()) +"','" +
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ex.getEvaluationEndDate()) +"')");
    	System.out.println("add- erster Versuch: /n " +"INSERT INTO exam(subjectId, subjectName, examDate, evaluationBegin, evaluationEnd) " +
				"VALUES ('"+ex.getSubjectIdentification()+"','"+ex.getSubjectName() +"','"
				+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ex.getExamDate()) +"','" +
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ex.getEvaluationBeginDate()) +"','" +
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ex.getEvaluationEndDate()) +"')");
    	
    	try{
			Statement st = ds.getStatement();
			ResultSet rs = st.executeQuery("SELECT TOP 1 Id FROM exam ORDER BY Id DESC");
			int examId = -1;
			if(rs.next()){
				examId = rs.getInt(1);
				System.out.println("Exam-Id = " + examId);
			}
			if(insertCorrect){
	    		insertCorrect = execute("INSERT INTO exam_user_rel(subjectId, userId) " +
	    								"VALUES ('"+examId+"','"+userId+"')");
	    	}
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    	
		return insertCorrect;
    }
     
    
    /**
     * 
     * @param ex
     * @return
     */
    public boolean update(int id, String subjectName, Date newBeginDate, Date newEndDate, Date examDate) {
    	boolean insertCorrect = execute("UPDATE exam " +
    			"SET subjectName='" + subjectName+"', " +
				"examDate='"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(examDate)+"'," +
				"evaluationBegin='"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newBeginDate)+"'," +
				"evaluationEnd='"+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(newEndDate)+"'" +
						"WHERE id="+ id );    	
		return insertCorrect;
    }
    
    
    /**
     * help function for the execution of sql-queries
     * @param sql
     * @return
     */
    private boolean execute(String sql) {

    	try {
            Statement stmt = ds.getStatement();
            stmt.execute(sql);
            if (stmt != null) {
                stmt.close();
            }
            
            return true;
        } catch (SQLException e) {
        	e.printStackTrace();
            return false;
        } finally {
            ds.close();
        }
    }
}


